<?php if (!defined('BASEPATH')) exit('No direct script access allowed');


/* 关于返回结果的json格式建议如下：
data:执行成功返回的数据
status:简单的表示业务执行的状态，如成功success，登录失败，更新失败等
code:业务执行状态的代码（可不设计）
message:表示详细信息
version:版本号 
*/


class HbzApi extends MY_Controller
{

    public function getnode($id)
    {
        $this->load->database();

        //只是取得节点本身
         $this->db->where('id', $id);
        $this->db->where('pub',1);      
         $query = $this->db->get('task'); 

        //一并取得定位用的父节点
       // $where = " t.id=$id ";
        //$sql = "SELECT t.*,p.title as ptitle,p.parent as ppid 
        //FROM task t 
        //LEFT JOIN task p ON t.grandfather=p.id 
        //WHERE ".$where." AND t.pub=1 ORDER BY parent desc,sort desc";  
        //$query = $this->db->query($sql);

        //  if ($query->num_rows() > 0) {
        //      $row = $query->row_array();
        //      $json_string = json_encode($row);
        //      echo $json_string;
        //  }
        //		print_r($row);
        if ($num_rows = $query->num_rows() > 0) {
            $result_array['data'] = $query->row_array();
            $result_array['status'] = 'success';
        } else {
            $result_array = array(
                'status' => 'nofound',
                'message' => '没有匹配的结果：' . $this->db->last_query()
            );
        }
        $this->array_to_json($result_array);
    }

    // 取得任务的直接子节点
    public function getchild($parent = 0)
    {
        $this->load->database();
        $this->db->where('parent', $parent);
        // $this->db->where('uid', $this->_login_userid);
        $this->db->where('pub',1);
        $this->db->order_by('sort', 'desc');
        $query = $this->db->get('task');
        if ($num_rows = $query->num_rows() > 0) {
            $result_array['data'] = $query->result_array();
            $result_array['status'] = 'success';
        } else {
            $result_array = array(
                'status' => 'nofound',
                'message' => '没有匹配的结果：' . $this->db->last_query()
            );
        }
        $this->array_to_json($result_array);
    }

    // 取得内联到该节点的列表
    public function getinlink($node = 0)
    {
        $this->load->database();
        $this->db->where('internal_link', $node);
        // $this->db->where('uid', $this->_login_userid);
        $this->db->where('pub',1);
        $this->db->order_by('sort', 'desc');
        $this->db->order_by('id', 'desc');
        $query = $this->db->get('task');
        if ($num_rows = $query->num_rows() > 0) {
            $result_array['data'] = $query->result_array();
            $result_array['status'] = 'success';
        } else {
            $result_array = array(
                'status' => 'nofound',
                'message' => '没有匹配的结果：' . $this->db->last_query()
            );
        }
        $this->array_to_json($result_array);
    }

    //使用关键词搜索标签、标题和备注
    public function searchByText()
    {
        $result_arr = array();
        $this->load->database();
        $where = '';
        $search_words = explode(' ', trim($_GET['search_words']));
        foreach ($search_words as $word) {
            if ($word) {
                $where .= ' AND (tags LIKE "%' . $word . '%" OR title LIKE "%' . $word . '%" OR comment LIKE "%' . $word . '%" ) ';
            }
        }
        if ($where) {
            $where = '1' . $where;
        } else {
            $result_arr['status'] = 'error';
            $result_arr['message'] = '不能执行一个空查询！';
            $json_string = json_encode($result_arr);
            echo $json_string;
            return; //防止出现空查询条件
        }
        // $this->db->where('pub', 1);
        // $this->db->where($where, NULL, FALSE);
        // $this->db->order_by('sort', 'desc');
        // $query = $this->db->get('task');

$sql = "SELECT p.title as ptitle,p.parent as ppid,t.* FROM
  (SELECT * FROM task WHERE ".$where." AND pub=1 ORDER BY parent desc,sort desc) t  LEFT JOIN task p ON t.grandfather=p.id ";
 
// $sql = "
// SELECT t.*,p.title as ptitle,p.nodetype as pnodetype FROM task t 
// LEFT JOIN task p ON t.grandfather=p.id 
// WHERE ".$where." AND pub=1 ORDER BY parent desc,sort desc"; 

$query = $this->db->query($sql);

        if (($num_rows = $query->num_rows()) > 0) {
            // $rows_arr = array();

            // foreach ($query->result_array() as $row) {
            //     //				$rows_arr[$row['id']]=$row;
            //     // $row['getway'] = $getway;
            //     $rows_arr[] = $row;
            // }
            // $result_arr['data'] = $rows_arr;
            // $query_arr = $query->result_array();

            $result_arr['data'] = $query->result_array();
            $result_arr['status'] = 'success';
            $result_arr['message'] = '共找到' . $num_rows . '个结果。';
        } else {
            $result_arr['status'] = 'noresult';
            $result_arr['message'] = '没有匹配的结果！';
        }
        $json_string = json_encode($result_arr);
        echo $json_string;
    }


    //取得网址书签类型的任务列表,调用getallchild
    public function geturlbook()
    {
        $this->getallchild(13);
    }
    //取得所有子孙节点的ID串（包含根节点本身，依靠MYSQL自定义函数）
    private function get_all_child_list($root)
    {
        $this->load->database();
        $query = $this->db->query("select getTaskChildLst('" . $root . "') as childlist;");
        if ($query->num_rows() > 0) {
            $row = $query->row();
            return $row->childlist;
        } else {
            return false;
        }
    }


    //取得所有子孙任务列表（使用上述get_all_child_list函数）
    private function getallchild1($parent = 0, $workarea = 0)
    {
        $childlist = $this->get_all_child_list($parent);

        if ($this->_login_userid) {
            $this->db->where('uid', $this->_login_userid);
        } else {
            $this->db->where('uid', 5);
        }
        if ($workarea) {
            $this->db->where('areaid', $workarea);
        } else {
        }
        $this->db->where('archive !=', '1'); //默认只取得所有未归档的任务
        $this->db->where(' FIND_IN_SET(id,"' . $childlist . '")>0 ', NULL, FALSE); //从子任务列表中取
        $this->db->order_by('sort desc,parent asc');
        $query = $this->db->get('task');
        if ($num_rows = $query->num_rows() > 0) {
            $result_array['data'] = $query->result_array();
            $result_array['status'] = 'success';
        } else {
            $result_array = array(
                'status' => 'nofound',
                'message' => '没有匹配的结果：' . $this->db->last_query()
            );
        }
        $this->array_to_json($result_array);
    }

    //不用mysql自定义函数,依赖parents字段
    private function getallchild2($parent = 0)
    {
        $uid = $this->_login_userid ? $this->_login_userid : '5';
        $sql = "select * from task where FIND_IN_SET(" . $parent . ",parents) AND uid=" . $uid . ";";
        $this->query_to_json($sql);
    }

    // 用mysql8.0递归函数来取得，不依赖parents字段
    private function getallchild3($parent = 0)
    {
        $uid = $this->_login_userid ? $this->_login_userid : '5';
        $sql = "with recursive t as ( select * from task where id = " . $parent . " union all select a.* from task a join t on a.parent = t.id where a.uid=" . $uid . " ) select * from t;";
        // $sql = "select task.* from task,team,team_user where task.tid=team.id and team_user.tid=team.id AND team.id=" . $teamid . " AND team_user.uid=" . ($this->_login_userid ? $this->_login_userid : '5') . " order by in_date desc;";
        $this->query_to_json($sql);
    }

    //取得所有子孙任务列表
    public function getallchild($parent = 0, $workarea = 0)
    {

        // 依赖mysql自定义函数
        // $this->getallchild1($parent,$workarea);
        //  依赖parents字段
        // $this->getallchild2($parent,$workarea);
        //  依赖mysql8.0递归语句
        $this->getallchild3($parent, $workarea);
    }

    //取得所有子孙任务的日程范围（使用上述get_all_child_list函数）
    public function getallchild_start_end($parent = 0, $workarea = 0)
    {
        $childlist = $this->get_all_child_list($parent);


        if ($this->_login_userid) {
            $this->db->where('uid', $this->_login_userid);
        } else {
            $this->db->where('uid', 5);
        }
        if ($workarea) {
            $this->db->where('areaid', $workarea);
        } else {
        }

        $this->db->select('min(start_date) as start,max(end_date) as end');
        //		$this->db->where('stage !=', 'finish');//默认只取得所有未完成的任务
        $this->db->where('archive !=', '1'); //默认只取得所有未归档的任务
        $this->db->where(' FIND_IN_SET(id,"' . $childlist . '")>0 ', NULL, FALSE); //从子任务列表中取
        // $this->db->order_by('sort desc,parent asc');
        $query = $this->db->get('task');
        if ($num_rows = $query->num_rows() > 0) {
            $result_array['data'] = $query->result_array();
            $result_array['status'] = 'success';
        } else {
            $result_array = array(
                'status' => 'nofound',
                'message' => '没有匹配的结果：' . $this->db->last_query()
            );
        }
        $this->array_to_json($result_array);
    }

    //通过预生成的paents列表，取得所有祖先任务，并以json返回
    public function getparents_by_parentslist($id)
    {
        $this->load->database();
        $sql = "SELECT t1.*,find_in_set(t1.id,t2.parents) as p_order FROM task t1,task t2 WHERE t2.id=" . $id . " AND find_in_set(t1.id,t2.parents) order by p_order desc;";
        $query = $this->db->query($sql);
        if ($num_rows = $query->num_rows() > 0) {
            $result_array['data'] = $query->result_array();
            $result_array['status'] = 'success';
        } else {
            $result_array['status'] = 'nofound';
        }
        //$result_array['data']=array_reverse($parents_array);
        $this->array_to_json($result_array);
    }

    //通过不断循环查询，取得所有祖先任务
    public function getparents($id)
    {
        $this->load->database();
        $parents_array = array();
        $opt = $id;
        do {
            $this->db->where('id', $opt);
            $query = $this->db->get('task');
            //			echo $this->db->last_query();
            if ($query->num_rows() > 0) {
                $row = $query->row_array();
                $result_message = var_export($row, true);
                $parents_array[] = $row;
                $opt = $row['parent'];
                continue;
            } else {
                $opt = NULL;
                break;
            }
        } while ($opt);
        $result_array['message'] = $result_message;
        $result_array['data'] = array_reverse($parents_array);
        $json_string = json_encode($result_array);
        echo $json_string;
    }
}
